"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : 普通列存表表结构变更，备机gs_gsc_table_detail函数gsc缓存功能验证
Create At   : 2024/01/18
Owner       : @peilinqian
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表插入数据，并查询;
    step5:备机查询; expect:操作成功
    step5.1:备机查看表syscache占用情况;
    step6:增加索引;
    step6.1:备机查看表及索引syscache占用情况;
    step6.2:备机使用索引查询;
    step6.3:备机查看表及索引syscache占用情况;
    step7:增加字段;
    step7.1:备机查看表及索引syscache占用情况;
    step7.2:备机使用索引查询;
    step7.3:备机查看表及索引syscache占用情况;
    step8:修改字段名称;
    step8.1:备机查看表及索引syscache占用情况;
    step8.2:备机使用索引查询;
    step9:删除字段;
    step9.1:备机查看表及索引syscache占用情况;
    step9.2:备机使用索引查询;
    step9.3:备机查看表及索引syscache占用情况;
    step10:删除索引;
    step10.1:备机查看syscache占用情况;
    step10.2:备机查询表;
    step10.3:备机查看表syscache占用情况;
    step11:删除表;
    step11.1:备机查看syscache占用情况;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表插入数据，并查询; expect:操作成功
    step5:备机查询; expect:操作成功
    step5.1:备机查看表syscache占用情况; expect:缓存信息正确
    step6:增加索引; expect:操作成功
    step6.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理
    step6.2:备机使用索引查询; expect:操作成功
    step6.3:备机查看表及索引syscache占用情况; expect:表及索引缓存信息正确---
    step7:增加字段; expect:操作成功
    step7.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理
    step7.2:备机使用索引查询; expect:操作成功
    step7.3:备机查看表及索引syscache占用情况; expect:缓存信息正确
    step8:修改字段名称; expect:操作成功
    step8.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理
    step8.2:备机使用索引查询; expect:操作成功
    step9:删除字段; expect:操作成功
    step9.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理
    step9.2:备机使用索引查询; expect:操作成功
    step9.3:备机查看表及索引syscache占用情况; expect:缓存信息正确
    step10:删除索引; expect:操作成功
    step10.1:备机查看syscache占用情况; expect:表的缓存信息清理
    step10.2:备机查询表; expect:操作成功
    step10.3:备机查看表syscache占用情况; expect:缓存信息正确
    step11:删除表; expect:操作成功
    step11.1:备机查看syscache占用情况; expect:表的缓存信息清理
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.db_name = 'db_gsc0072'
        self.tb_name = "tb_gsc0072"
        self.com = Common()
        self.constant = Constant()
        self.sta_sh = CommonSH('Standby1DbUser')

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表插入数据，并查询; expect:操作成功----'
        self.log.info(step_txt)
        self.create_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name} " \
            f"(id int not null, aa varchar(10) ,id2 int) " \
            f"with (orientation= column);" \
            f"insert into {self.tb_name} select generate_series(1,20000), " \
            f"generate_series(1,20000)||'1a', 11;" \
            f"select count(*) from {self.tb_name};"
        result = pri_sh.execut_db_sql(self.create_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)
        self.assertIn('INSERT 0 20000', result, "执行失败" + step_txt)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        self.log.info('----等待主备同步----')
        self.sta_sh.check_location_consistency('standby', node_num)

        step_txt = '----step5:备机查询; expect:操作成功----'
        self.log.info(step_txt)
        self.select_sql = f"select count(*) from {self.tb_name} where id>999;"
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step5.1:备机查看表syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_sql = f"select * from gs_gsc_table_detail() where " \
            f"relname like '{self.tb_name}%';" \
            f"select * from gs_gsc_dbstat_info() " \
            f"where database_name = '{self.db_name}';"
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2'", check_result, '执行失败:' + step_txt)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step6:增加索引; expect:操作成功----'
        self.log.info(step_txt)
        create_sql = f"create index {self.tb_name}_index on " \
            f"{self.tb_name}(id);"
        result = pri_sh.execut_db_sql(create_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step6.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step6.2:备机使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        self.select_sql = f"select count(*) from {self.tb_name} where id>999;"
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step6.3:备机查看表及索引syscache占用情况; expect:表及索引缓存信息正确----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2'", check_result, '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step7:增加字段; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} add " \
            f"(address varchar(100),sex char);"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step7.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step7.2:备机使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step7.3:备机查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'id','aa','id2','address','sex'", check_result,
                      '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step8:修改字段名称; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} rename id to new_id;"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step8.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step8.2:备机使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        self.select_sql = f"select count(*) from {self.tb_name} " \
            f"where new_id>999;"
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----ste8.3:备机查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','address','sex'", check_result,
                      '执行失败:' + step_txt)
        self.assertIn("2 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step9:删除字段; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"alter table {self.tb_name} drop column address;"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step9.1:备机查看表及索引syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step9.2:备机使用索引查询; expect:操作成功----'
        self.log.info(step_txt)
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step9.3:备机查看表及索引syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','........pg.dropped.4........',"
                      "'sex'", check_result, '执行失败:' + step_txt)
        self.assertIn("2 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step10:删除索引; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop index {self.tb_name}_index;"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('DROP INDEX', result, "执行失败" + step_txt)
        step_txt = '----step10.1:备机查看syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)
        step_txt = '----step10.2:备机查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = self.sta_sh.execut_db_sql(self.select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step10.3:备机查看表syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("'new_id','aa','id2','........pg.dropped.4........',"
                      "'sex'", check_result, '执行失败:' + step_txt)
        self.assertIn("1 row", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

        step_txt = '----step11:删除表; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step11.1:备机查看syscache占用情况; expect:表的缓存信息清理----'
        self.log.info(step_txt)
        check_result = self.sta_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        self.assertIn("0 rows", check_result.splitlines()[-6],
                      '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            self.status_result = pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
